﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.Basic;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.Basic
{
    public class DutyDao
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dutyEntity"></param>
        /// <returns>刚插入的主键</returns>
        public int AddEntity(DutyEntity dutyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@duty_id", dutyEntity.duty_id));
            parameterList.Add(new SqlParameter("@duty_name", dutyEntity.duty_name));
            parameterList.Add(new SqlParameter("@duty_lev", dutyEntity.duty_lev));
            parameterList.Add(new SqlParameter("@duty_descn", dutyEntity.duty_descn));
            parameterList.Add(new SqlParameter("@dept_id", dutyEntity.dept_id));


            string sql = "insert into jc_duty (duty_name,duty_lev,duty_descn,dept_id) values " +
                " (@duty_name,@duty_lev,@duty_descn,@dept_id); " +
                " select @@identity";
            string id = DBExecuteUtil.getUniqueResult(sql, parameterList.ToArray());
            return int.Parse(id);
        }

        public void DleteEntity(int duty_id)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@duty_id", duty_id));
            string sql = "delete from jc_duty where duty_id=@duty_id ";
            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public void UpdateEntity(DutyEntity dutyEntity)
        {
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@duty_id", dutyEntity.duty_id));
            parameterList.Add(new SqlParameter("@duty_name", dutyEntity.duty_name));
            parameterList.Add(new SqlParameter("@duty_lev", dutyEntity.duty_lev));
            parameterList.Add(new SqlParameter("@duty_descn", dutyEntity.duty_descn));
            parameterList.Add(new SqlParameter("@dept_id", dutyEntity.dept_id));

            string sql = "update jc_duty set duty_name=@duty_name,duty_lev=@duty_lev,duty_descn=@duty_descn,dept_id=@dept_id where duty_id=@duty_id ";

            DBExecuteUtil.executeSqlWithPar(sql, parameterList.ToArray());
        }

        public DutyEntity QueryEntity(int duty_id)
        {
            DutyEntity dutyEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@duty_id", duty_id));
            string sql = "select duty_id,duty_name,duty_lev,duty_descn,dept_id  from jc_duty where duty_id=@duty_id";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            dutyEntity = new DutyEntity();
            dutyEntity.duty_id = int.Parse(result[0][0]);
            dutyEntity.duty_name = result[0][1];
            dutyEntity.duty_lev = int.Parse(result[0][2]);
            dutyEntity.duty_descn = result[0][3];
            dutyEntity.dept_id = int.Parse(result[0][4]);
            return dutyEntity;
        }

        public DutyEntity QueryEntity(string duty_name)
        {
            DutyEntity dutyEntity = null;
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@duty_name", duty_name));
            string sql = "select duty_id,duty_name,duty_lev,duty_descn,dept_id  from jc_duty where duty_name=@duty_name";

            var result = DBExecuteUtil.querySqlArray(sql, parameterList.ToArray());
            if (result == null) return null;

            dutyEntity = new DutyEntity();
            dutyEntity.duty_id = int.Parse(result[0][0]);
            dutyEntity.duty_name = result[0][1];
            dutyEntity.duty_lev = int.Parse(result[0][2]);
            dutyEntity.duty_descn = result[0][3];
            dutyEntity.dept_id = int.Parse(result[0][4]);
            return dutyEntity;
        }

        /// <summary>
        /// 查询部门下的职位
        /// </summary>
        /// <param name="dept_id"></param>
        /// <returns></returns>
        public DataTable QueryAll()
        {
            string sql = "select duty_id 职位编码,duty_name 职位名称,duty_lev 等级,duty_descn 描述 from jc_duty";
            DataTable dt = DBExecuteUtil.querySqlTable(sql);
            return dt;
        }
    }
}
